const connection = require("../app/database");

class MomentService {
  //创建
  async create(userId, content) {
    //写入数据库
    //预编译语句
    const statement = `INSERT INTO moment (content, user_id) VALUES (?, ?);`;

    //插入数据库
    const [res] = await connection.execute(statement, [content, userId]);
    return res;
  }
  // 列表
  async getMomentList(offset = 0, size = 10) {
    /*       const statement = `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
      JSON_OBJECT('id', u.id, 'name', u.name, 'createTime', u.createAt, 'updateTime', u.updateAt) user
    FROM moment m
    LEFT JOIN user u ON u.id = m.user_id
    LIMIT ? OFFSET ? ;`; */

    /*     const statement = `
  SELECT moment.id id ,moment.content content,moment.createAt createTime ,moment.updateAt updateTime,
JSON_OBJECT('id',user.id,"name",user.name,'createTime',user.createAt,'updateTime',user.updateAt),
(SELECT COUNT(*) FROM comment WHERE comment.moment_id=moment.id) commentCount
FROM moment  LEFT JOIN user ON user.id=moment.user_id
LIMIT ? OFFSET ?;
  `; */

    const statement = `
  SELECT moment.id id ,moment.content content,moment.createAt createTime ,moment.updateAt updateTime,
JSON_OBJECT('id',user.id,"name",user.name,'createTime',user.createAt,'updateTime',user.updateAt,'avatarUrl',user.avatar_url) user,
(SELECT COUNT(*) FROM comment WHERE comment.moment_id=moment.id) commentCount,
(SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id=moment.id) labelCount
FROM moment  LEFT JOIN user ON user.id=moment.user_id
LIMIT ? OFFSET ?;
  `;

    //有坑，需要转换为字符串
    const [result] = await connection.execute(statement, [String(size), String(offset)]);
    return result;
  }

  // 详情
  async getMomentById(momentId) {
    /*       const statement = `SELECT
      m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
      JSON_OBJECT('id', u.id, 'name', u.name, 'createTime', u.createAt, 'updateTime', u.updateAt) user
    FROM moment m
    LEFT JOIN user u ON u.id = m.user_id
    WHERE m.id = ?;`; */

    /*     const statement = `SELECT 
  m.id id,m.content content,m.createAt createTime ,m.updateAt updateTime,
  JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'updateTime',u.createAt) user,
  JSON_ARRAYAGG(JSON_OBJECT('id',c.id,'content',c.content,'commentId',c.comment_id,'user',JSON_OBJECT('id',cu.id,'name',cu.name))) comments
  FROM moment m
  LEFT JOIN user u ON u.id=m.user_id
  LEFT JOIN comment c ON c.moment_id=m.id
  LEFT JOIN user cu ON cu.id=c.user_id
  WHERE m.id=?
  GROUP BY m.id;`; */

    const statement = `
    SELECT 
    m.id id,m.content content,m.createAt createTime ,m.updateAt updateTime,
    JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'updateTime',u.createAt) user,
    (
    SELECT JSON_ARRAYAGG(JSON_OBJECT('id',c.id,'content',c.content,'commentId',c.comment_id,'user',JSON_OBJECT('id',cu.id,'name',cu.name))) 
    FROM comment c
    LEFT JOIN user cu ON C.user_id=cu.id
    WHERE c.moment_id=m.id
    ) comments,
    JSON_ARRAYAGG(JSON_OBJECT('id',label.id,'name',label.name)) labels
    FROM moment m
    LEFT JOIN user u ON u.id=m.user_id
    
    LEFT JOIN moment_label ml ON ml.moment_id=m.id
    LEFT JOIN label ON ml.label_id=label.id
    WHERE m.id=?
    GROUP BY m.id;
  `;

    const [result] = await connection.execute(statement, [momentId]);
    return result;
  }

  // 更新
  async update(content, momentId) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`;
    const [result] = await connection.execute(statement, [content, momentId]);
    return result;
  }

  // 删除
  async remove(momentId) {
    const statement = `DELETE FROM moment WHERE id = ?`;
    const [result] = await connection.execute(statement, [momentId]);
    return result;
  }

  // 是否有标签
  async hasLabel(momentId, labelId) {
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?`;
    const [result] = await connection.execute(statement, [momentId, labelId]);
    return result[0] ? true : false;
  }

  //添加标签
  async addLabel(momentId, labelId) {
    const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`;
    const [result] = await connection.execute(statement, [momentId, labelId]);
    return result;
  }
}

module.exports = new MomentService();
